﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for DBHelper
/// </summary>
public class DBHelper
{
    private string connectionString;
    private string ConnectionType;
    private CommandType _cmdType;
    private string parameterPrefix;

	public DBHelper()
	{        
        _cmdType = CommandType.StoredProcedure;
        parameterPrefix = "@";
        if (ConfigurationManager.AppSettings["DBType"] != null)
        {
            ConnectionType = ConfigurationManager.AppSettings["DBType"];
            if (ConnectionType == "MySQL")
            {
                _cmdType = CommandType.Text;
                parameterPrefix = "var_";
            }
        }
        connectionString = Constants.GetConstInstance(ConnectionType).ConnectionStringName;        
	}

    public void SaveEBookDetails(int Id, string Title,string subTitle, string Summary, string Edition, string Authors, int PublisherId, int PublishedYear
        ,int CategoryId, string Link, byte[] ThumbnailImage, int CreatedBy)
    {
        List<MyDBParameter> lstParameters = new List<MyDBParameter>();

        lstParameters.Add(new MyDBParameter(parameterPrefix + "Id", Id));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "Title", Title));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "SubTitle", subTitle));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "Summary", Summary));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "Edition", Edition));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "Author", Authors));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "PublisherId", PublisherId));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "PublishedYear", PublishedYear));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "CategoryId", CategoryId));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "Link", Link));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "ThumbnailImage", ThumbnailImage));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "CreatedBy", CreatedBy));
        lstParameters.Add(new MyDBParameter(parameterPrefix + "CreatedDate", DateTime.Today));

        IDataReaderFactory dbReader = DataReaderFactory.GetDBReaderInstance(connectionString, ConnectionType);
        dbReader.ExecuteNonQuery(Constants.GetConstInstance(ConnectionType).SaveEBookSPName, CommandType.StoredProcedure, lstParameters.ToArray());
    }

    public List<Category> GetAllCategories()
    {
        List<Category> lstCategory = new List<Category>();

        IDataReaderFactory dbReader = DataReaderFactory.GetDBReaderInstance(connectionString, ConnectionType);
        DataTable dtCat = dbReader.GetDataTable(Constants.GetConstInstance(ConnectionType).GetCategorySPName, _cmdType);

        lstCategory = (from row in dtCat.AsEnumerable()
                       select new Category
                       {
                           Id = Convert.ToInt32(row["Id"]),
                           CategoryName = row["CategoryName"].ToString()
                       }).ToList<Category>();


        return lstCategory;
    }

    public List<Publisher> GetAllPublishers()
    {
        List<Publisher> lstPublisher = new List<Publisher>();

        IDataReaderFactory dbReader = DataReaderFactory.GetDBReaderInstance(connectionString, ConnectionType);
        DataTable dtCat = dbReader.GetDataTable(Constants.GetConstInstance(ConnectionType).GetPublisherSPName, _cmdType);

        lstPublisher = (from row in dtCat.AsEnumerable()
                       select new Publisher
                       {
                           Id = Convert.ToInt32(row["Id"]),
                           PublisherName = row["PublisherName"].ToString()
                       }).ToList<Publisher>();


        return lstPublisher;
    }

    public List<EbookDetail> GetAllEBooks()
    {
        List<EbookDetail> lstEBook = new List<EbookDetail>();

        IDataReaderFactory dbReader = DataReaderFactory.GetDBReaderInstance(connectionString, ConnectionType);
        DataTable dtCat = dbReader.GetDataTable(Constants.GetConstInstance(ConnectionType).GetAllBookSPName, _cmdType);

        lstEBook = (from row in dtCat.AsEnumerable()
                    select new EbookDetail
                    {
                        Id = Convert.ToInt32(row["Id"]),
                        Title = Convert.ToString(row["Title"]),
                        SubTitle = Convert.ToString(row["SubTitle"]),
                        Summary = Convert.ToString(row["Summary"]),
                        Edition = Convert.ToString(row["Edition"]),
                        Authors = Convert.ToString(row["Author"]),
                        PublisherId = Convert.ToInt32(row["Publisher"]),
                        PublishedYear = Convert.ToInt32(row["PublishedYear"]),
                        CategoryId = Convert.ToInt32(row["CategoryId"]),
                        ThumbnailId = row["ThumbnailId"] == DBNull.Value ? 0 : Convert.ToInt32(row["ThumbnailId"]),
                        Link = row["Link"].ToString(),
                        CreatedDate = Convert.ToDateTime(row["CreatedDate"]),
                        CreatedBy = Convert.ToInt32(row["CreatedBy"])
                    }).ToList<EbookDetail>();


        return lstEBook;
    }

    public byte[] GetThumbnail(int Id)
    {
        byte[] btImage =null;
        IDataReaderFactory dbReader = DataReaderFactory.GetDBReaderInstance(connectionString, ConnectionType);
        object imageData = dbReader.ExecureScalar(Constants.GetConstInstance(ConnectionType).GetThumbnailSPName, CommandType.StoredProcedure, new MyDBParameter(parameterPrefix + "Id", Id));
        if(imageData !=DBNull.Value)
            btImage = (byte[])imageData;
      
        return btImage;
    }

    public List<Author> GetAllAuthors()
    {
        List<Author> lstAuthor = new List<Author>();

        IDataReaderFactory dbReader = DataReaderFactory.GetDBReaderInstance(connectionString, ConnectionType);
        DataTable dtCat = dbReader.GetDataTable(Constants.GetConstInstance(ConnectionType).GetAllAuthorSPName, _cmdType);

        lstAuthor = (from row in dtCat.AsEnumerable()
                        select new Author
                        {
                            Id = Convert.ToInt32(row["Id"]),
                            AuthorName = row["Author"].ToString()
                        }).ToList<Author>();


        return lstAuthor;
    }
}